﻿using System;
using System.Collections.Generic;
using System.Data;
//using MySql.Data.MySqlClient;
using System.Data.OracleClient;

namespace CCPSIPMsgPush
{
    public class MsgPushWoker
    {
        private const int kNotifyExpiryDays = 21;
        private const string kSystemSender = "System";

        /* MySQL寫法
        private const string kMySqlServer = "192.168.52.80";
        private const int kMySqlPort = 3309;
        private const string kMySqlUserId = "ccpsipuser";
        private static byte[] kMySqlKey = { 0x53, 0x59, 0x33, 0x43, 0x36, 0x38, 0x64, 0x33,
                                        0x78, 0x59, 0x57, 0x34, 0x61, 0x4A };
        private const string kMySqlDatabase = "ccpsip";


        /// <summary>
        /// 取得 CCPSIP 的 MySQL 連線字串
        /// </summary>
        /// <returns>MySQL 連線字串</returns>
        private static string getCcpsipConnStr()
        {
            string connStrMySql = String.Format(
              "server={0};port={1};database={2};uid={3};pwd={4};charset=utf8mb4;pooling=true",
              kMySqlServer, kMySqlPort, kMySqlDatabase, kMySqlUserId,
              System.Text.Encoding.UTF8.GetString(kMySqlKey));
            return connStrMySql;
        }
        */

        /// <summary>取得Oracle連線字串</summary>
        private static string GetConnStr()
        {
            return "SERVER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.52.195)(PORT=1521)))(CONNECT_DATA =(SERVER=DEDICATED)(SID=UNIDB)));uid=ISENDER;pwd=aKhJJFA2sWfEsQz0Zm5iwQi9gjiVND";
        }

        /// <summary>
        /// 檢查待推播訊息並傳送
        /// </summary>
        public static void DoWork()
        {
            //MySqlConnection connMySql = new MySqlConnection(getCcpsipConnStr());
            OracleConnection connOracle = new OracleConnection(GetConnStr());
            try
            {
                //connMySql.Open();
                connOracle.Open();

                DateTime expiryTime = DateTime.Now.AddDays(kNotifyExpiryDays);
                int expirySeconds = kNotifyExpiryDays * 24 * 3600;

                //【01_P1】取得待推播的 One-to-One Message

                /* MySQL 寫法
                string selStr1 = "SELECT pid, Sender, Receiver, MsgType, MsgPreview"
                  + " FROM ccpsip.msg_one"
                  + " WHERE Status='0'"
                  + " ORDER BY CreateTime, pid";
                MySqlCommand selCmd1 = new MySqlCommand(selStr1, connMySql);
                MySqlDataAdapter adapter1 = new MySqlDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);
                */

                string selStr1 = "SELECT pid, Sender, Receiver, MsgType, MsgPreview"
                  + " FROM msg_one"
                  + " WHERE Status='0'"
                  + " ORDER BY CreateTime, pid";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
                DataSet dataSet1 = new DataSet();
                adapter1.Fill(dataSet1);

                List<string> PIDList = new List<string>();

                foreach (DataRow row in dataSet1.Tables[0].Rows)
                {
                    UInt32 MsgID = Convert.ToUInt32(row["pid"]);
                    PIDList.Add(Convert.ToString(MsgID));
                    string Sender = Convert.ToString(row["Sender"]);
                    string Receiver = Convert.ToString(row["Receiver"]);
                    string MsgType = Convert.ToString(row["MsgType"]);
                    string MsgPreview = Convert.ToString(row["MsgPreview"]);

                    DevicePushToken pToken = getPushToken(Receiver, connOracle);

                    if (pToken != null)
                    {
                        if (pToken.PushType == "IP" || pToken.PushType == "IS")
                        {
                            //iOS APNs Push
                            UInt32? badgeNum = getUnreadMsgCount(Receiver, connOracle);
                            bool isDev = (pToken.PushType == "IS");

                            if (isNotifyOn(Receiver, Sender, connOracle))
                            {
                                Kit.ApnsKit.SendMsgPushInHouse(MsgType, MsgPreview, badgeNum, Sender, pToken.PushToken, isDev, MsgID, expiryTime);
                                Kit.ApnsKit.SendMsgPushAppStore(MsgType, MsgPreview, badgeNum, Sender, pToken.PushToken, isDev, MsgID, expiryTime);
                            }
                            else
                            {
                                Kit.ApnsKit.SendMsgPushInHouse(MsgType, null, badgeNum, Sender, pToken.PushToken, isDev, MsgID, expiryTime);
                                Kit.ApnsKit.SendMsgPushAppStore(MsgType, null, badgeNum, Sender, pToken.PushToken, isDev, MsgID, expiryTime);
                            }
                        }
                        else if (pToken.PushType == "A")
                        {
                            //Android GCM Push
                            Kit.GCMKit.SendMsgPush(MsgType, MsgPreview, Sender, pToken.PushToken, expirySeconds);
                        }
                        else if (pToken.PushType == "B")
                        {
                            //百度雲推送
                            Baidu.BaiduKit.SendMsgPush(MsgType, MsgPreview, Sender, pToken.PushToken, expirySeconds);
                        }
                    }
                }


                //【02_P1】更新 One-to-One Message 的通知狀態
                //
                if (PIDList.Count > 0)
                {
                    string updStr2 = "UPDATE msg_one SET Status='2'"
                      + " WHERE Status='0' AND pid IN "
                      + string.Format("({0})", string.Join(",", PIDList.ToArray()));
                    OracleCommand updCmd2 = new OracleCommand(updStr2, connOracle);
                    updCmd2.ExecuteNonQuery();
                }


                //【03_P2】取得待推播的 Group Message
                //
                string selStr3 = "SELECT m.pid, m.GroupID, c.ClientGID, m.MsgType, m.MsgPreview"
                  + " FROM group_msg m, group_chat c"
                  + " WHERE m.Status='0' AND m.GroupID=c.pid"
                  + " ORDER BY m.CreateTime, m.pid";
                OracleCommand selCmd3 = new OracleCommand(selStr3, connOracle);
                OracleDataAdapter adapter3 = new OracleDataAdapter(selCmd3);
                DataSet dataSet3 = new DataSet();
                adapter3.Fill(dataSet3);

                PIDList.Clear();

                foreach (DataRow row in dataSet3.Tables[0].Rows)
                {
                    UInt32 GMsgID = Convert.ToUInt32(row["pid"]);
                    PIDList.Add(Convert.ToString(GMsgID));
                    UInt32 GroupID = Convert.ToUInt32(row["GroupID"]);
                    string ClientGID = Convert.ToString(row["ClientGID"]);
                    string MsgType = Convert.ToString(row["MsgType"]);
                    string MsgPreview = Convert.ToString(row["MsgPreview"]);


                    //【04_P2】取得未讀 Group Message 的成員
                    //
                    string selStr4 = "SELECT Receiver FROM group_msg_read"
                      + " WHERE GMsgID=:GMsgID AND ReadTime IS NULL"
                      + " ORDER BY Receiver";
                    OracleCommand selCmd4 = new OracleCommand(selStr4, connOracle);
                    selCmd4.Parameters.Add(":GMsgID", OracleType.Number).Value = GMsgID;
                    OracleDataAdapter adapter4 = new OracleDataAdapter(selCmd4);
                    DataSet dataSet4 = new DataSet();
                    adapter4.Fill(dataSet4);

                    foreach (DataRow gmrRow in dataSet4.Tables[0].Rows)
                    {
                        string Receiver = Convert.ToString(gmrRow["Receiver"]);

                        DevicePushToken pToken = getPushToken(Receiver, connOracle);

                        if (pToken != null)
                        {
                            if (pToken.PushType == "IP" || pToken.PushType == "IS")
                            {
                                //iOS APNs Push
                                UInt32? badgeNum = getUnreadMsgCount(Receiver, connOracle);
                                bool isDev = (pToken.PushType == "IS");

                                if (isNotifyOn(Receiver, ClientGID, connOracle))
                                {
                                    Kit.ApnsKit.SendMsgPushInHouse(MsgType, MsgPreview, badgeNum, ClientGID, pToken.PushToken, isDev, GMsgID, expiryTime);
                                    Kit.ApnsKit.SendMsgPushAppStore(MsgType, MsgPreview, badgeNum, ClientGID, pToken.PushToken, isDev, GMsgID, expiryTime);
                                }
                                else
                                {
                                    Kit.ApnsKit.SendMsgPushInHouse(MsgType, null, badgeNum, ClientGID, pToken.PushToken, isDev, GMsgID, expiryTime);
                                    Kit.ApnsKit.SendMsgPushAppStore(MsgType, null, badgeNum, ClientGID, pToken.PushToken, isDev, GMsgID, expiryTime);
                                }
                            }
                            else if (pToken.PushType == "A")
                            {
                                //Android GCM Push
                                Kit.GCMKit.SendMsgPush(MsgType, MsgPreview, ClientGID, pToken.PushToken, expirySeconds);
                            }
                            else if (pToken.PushType == "B")
                            {
                                //百度雲推送
                                Baidu.BaiduKit.SendMsgPush(MsgType, MsgPreview, ClientGID, pToken.PushToken, expirySeconds);
                            }
                        }
                    }
                }


                //【05_P2】更新 Group Message 的通知狀態
                //
                if (PIDList.Count > 0)
                {
                    string updStr5 = "UPDATE group_msg SET Status='2'"
                      + " WHERE Status='0' AND pid IN "
                      + string.Format("({0})", string.Join(",", PIDList.ToArray()));
                    OracleCommand updCmd5 = new OracleCommand(updStr5, connOracle);
                    updCmd5.ExecuteNonQuery();
                }


                //【06_P3】取得待通知的註冊確認
                //
                string selStr6 = "SELECT pid, ApplyStatus, FullName,"
                  + " FriendCountry, FriendPhone, FriendName"
                  + " FROM reg_apply"
                  + " WHERE ApplyStatus IN ('1','Y','N') AND Pushed='N'"
                  + " ORDER BY CreateTime, pid";
                OracleCommand selCmd6 = new OracleCommand(selStr6, connOracle);
                OracleDataAdapter adapter6 = new OracleDataAdapter(selCmd6);
                DataSet dataSet6 = new DataSet();
                adapter6.Fill(dataSet6);

                PIDList.Clear();

                foreach (DataRow row in dataSet6.Tables[0].Rows)
                {
                    UInt32 ApplyID = Convert.ToUInt32(row["pid"]);
                    PIDList.Add(Convert.ToString(ApplyID));
                    string ApplyStatus = Convert.ToString(row["ApplyStatus"]);

                    if (ApplyStatus == "1")
                    {
                        //【06-1】'1'=門號已認證，等待朋友確認
                        //
                        string FullName = Convert.ToString(row["FullName"]);
                        string FriendCountry = Convert.ToString(row["FriendCountry"]);
                        string FriendPhone = Convert.ToString(row["FriendPhone"]);

                        string MsgType = "AC";
                        string PushMsg = string.Format("{0} wants to use iSender, please confirm.", FullName);

                        DevicePushToken[] tokenArray = getPushTokens(FriendCountry, FriendPhone, connOracle);
                        AuthPushToken authToken = getAuthPushToken(ApplyID, connOracle);

                        foreach (DevicePushToken pToken in tokenArray)
                        {
                            if (authToken != null && pToken.PushToken == authToken.PushToken)
                                continue;

                            if (pToken.PushType == "IP" || pToken.PushType == "IS")
                            {
                                //iOS APNs Push
                                bool isDev = (pToken.PushType == "IS");
                                Kit.ApnsKit.SendMsgPushInHouse(MsgType, PushMsg, null, kSystemSender, pToken.PushToken, isDev, ApplyID, expiryTime);
                                Kit.ApnsKit.SendMsgPushAppStore(MsgType, PushMsg, null, kSystemSender, pToken.PushToken, isDev, ApplyID, expiryTime);
                            }
                            else if (pToken.PushType == "A")
                            {
                                //Android GCM Push
                                Kit.GCMKit.SendMsgPush(MsgType, PushMsg, kSystemSender, pToken.PushToken, expirySeconds);
                            }
                            else if (pToken.PushType == "B")
                            {
                                //百度雲推送
                                Baidu.BaiduKit.SendMsgPush(MsgType, PushMsg, kSystemSender, pToken.PushToken, expirySeconds);
                            }
                        }
                    }
                    else if (ApplyStatus == "Y" || ApplyStatus == "N")
                    {
                        //【06-2】'Y'=已通過朋友確認 / 'N'=朋友退回申請
                        //
                        string FriendName = Convert.ToString(row["FriendName"]);

                        string MsgType = "AN";
                        string PushMsg = string.Format("{0} rejected your registration.", FriendName);

                        if (ApplyStatus == "Y")
                        {
                            MsgType = "AY";
                            PushMsg = string.Format("{0} confirmed your registration.", FriendName);
                        }

                        AuthPushToken authToken = getAuthPushToken(ApplyID, connOracle);

                        if (authToken != null)
                        {
                            if (authToken.PushType == "IP" || authToken.PushType == "IS")
                            {
                                //iOS APNs Push
                                bool isDev = (authToken.PushType == "IS");
                                Kit.ApnsKit.SendMsgPushInHouse(MsgType, PushMsg, null, kSystemSender, authToken.PushToken, isDev, ApplyID, expiryTime);
                                Kit.ApnsKit.SendMsgPushAppStore(MsgType, PushMsg, null, kSystemSender, authToken.PushToken, isDev, ApplyID, expiryTime);
                            }
                            else if (authToken.PushType == "A")
                            {
                                //Android GCM Push
                                Kit.GCMKit.SendMsgPush(MsgType, PushMsg, kSystemSender, authToken.PushToken, expirySeconds);
                            }
                            else if (authToken.PushType == "B")
                            {
                                //百度雲推送
                                Baidu.BaiduKit.SendMsgPush(MsgType, PushMsg, kSystemSender, authToken.PushToken, expirySeconds);
                            }
                        }


                        //【07_P3】清除待確認區的Device Token
                        //
                        string delStr7 = "DELETE FROM reg_auth_device WHERE ApplyID=:ApplyID";
                        OracleCommand delCmd7 = new OracleCommand(delStr7, connOracle);
                        delCmd7.Parameters.Add(":ApplyID", OracleType.Number).Value = ApplyID;
                        delCmd7.ExecuteNonQuery();
                    }
                }


                //【08_P3】更新註冊確認的通知狀態
                //
                if (PIDList.Count > 0)
                {
                    string updStr8 = "UPDATE reg_apply SET Pushed='Y'"
                      + " WHERE Pushed='N' AND pid IN "
                      + string.Format("({0})", string.Join(",", PIDList.ToArray()));
                    OracleCommand updCmd8 = new OracleCommand(updStr8, connOracle);
                    updCmd8.ExecuteNonQuery();
                }


                //【09_P4】取得待通知的禮物貼圖
                //
                string selStr9 = "SELECT pid, Sender, Recipient, StickerID"
                  + " FROM sticker_gift"
                  + " WHERE Status='0'"
                  + " ORDER BY CreateTime, pid";
                OracleCommand selCmd9 = new OracleCommand(selStr9, connOracle);
                OracleDataAdapter adapter9 = new OracleDataAdapter(selCmd9);
                DataSet dataSet9 = new DataSet();
                adapter9.Fill(dataSet9);

                PIDList.Clear();

                foreach (DataRow row in dataSet9.Tables[0].Rows)
                {
                    UInt32 GiftID = Convert.ToUInt32(row["pid"]);
                    PIDList.Add(Convert.ToString(GiftID));

                    const string MsgType = "SG";
                    string Sender = Convert.ToString(row["Sender"]);
                    string Recipient = Convert.ToString(row["Recipient"]);
                    string StickerID = Convert.ToString(row["StickerID"]);
                    string PushMsg = string.Format("{0} sent you a gift sticker \"{1}\"!", Kit.SipKit.getCustomName(Sender), StickerID);

                    DevicePushToken pToken = getPushToken(Recipient, connOracle);

                    if (pToken != null)
                    {
                        if (pToken.PushType == "IP" || pToken.PushType == "IS")
                        {
                            //iOS APNs Push
                            bool isDev = (pToken.PushType == "IS");
                            Kit.ApnsKit.SendMsgPushInHouse(MsgType, PushMsg, null, Sender, pToken.PushToken, isDev, GiftID, expiryTime);
                            Kit.ApnsKit.SendMsgPushAppStore(MsgType, PushMsg, null, Sender, pToken.PushToken, isDev, GiftID, expiryTime);
                        }
                        else if (pToken.PushType == "A")
                        {
                            //Android GCM Push
                            Kit.GCMKit.SendMsgPush(MsgType, PushMsg, Sender, pToken.PushToken, expirySeconds);
                        }
                        else if (pToken.PushType == "B")
                        {
                            //百度雲推送
                            Baidu.BaiduKit.SendMsgPush(MsgType, PushMsg, Sender, pToken.PushToken, expirySeconds);
                        }
                    }
                }


                //【10_P4】更新禮物貼圖的通知狀態
                //
                if (PIDList.Count > 0)
                {
                    string updStr10 = "UPDATE sticker_gift SET Status='2'"
                      + " WHERE Status='0' AND pid IN "
                      + string.Format("({0})", string.Join(",", PIDList.ToArray()));
                    OracleCommand updCmd10 = new OracleCommand(updStr10, connOracle);
                    updCmd10.ExecuteNonQuery();
                }


                //【11_P5】取得待通知的 vCard 交換請求
                //
                string selStr11 = "SELECT pid, Scanner, Replier FROM vcard_send"
                  + " WHERE Status='0' AND Pushed='N'"
                  + " ORDER BY InitTime, pid";
                OracleCommand selCmd11 = new OracleCommand(selStr11, connOracle);
                OracleDataAdapter adapter11 = new OracleDataAdapter(selCmd11);
                DataSet dataSet11 = new DataSet();
                adapter11.Fill(dataSet11);

                PIDList.Clear();

                foreach (DataRow row in dataSet11.Tables[0].Rows)
                {
                    UInt32 vCardExchID = Convert.ToUInt32(row["pid"]);
                    PIDList.Add(Convert.ToString(vCardExchID));

                    const string MsgType = "VCR";
                    string Scanner = Convert.ToString(row["Scanner"]);
                    string Replier = Convert.ToString(row["Replier"]);
                    string PushMsg = string.Format("{0} want to exchange vCards with you.", Kit.SipKit.getCustomName(Scanner));

                    DevicePushToken pToken = getPushToken(Replier, connOracle);

                    if (pToken != null)
                    {
                        if (pToken.PushType == "IP" || pToken.PushType == "IS")
                        {
                            //iOS APNs Push
                            bool isDev = (pToken.PushType == "IS");
                            Kit.ApnsKit.SendMsgPushInHouse(MsgType, PushMsg, null, Scanner, pToken.PushToken, isDev, vCardExchID, expiryTime);
                            Kit.ApnsKit.SendMsgPushAppStore(MsgType, PushMsg, null, Scanner, pToken.PushToken, isDev, vCardExchID, expiryTime);
                        }
                        else if (pToken.PushType == "A")
                        {
                            //Android GCM Push
                            Kit.GCMKit.SendMsgPush(MsgType, PushMsg, Scanner, pToken.PushToken, expirySeconds);
                        }
                        else if (pToken.PushType == "B")
                        {
                            //百度雲推送
                            Baidu.BaiduKit.SendMsgPush(MsgType, PushMsg, Scanner, pToken.PushToken, expirySeconds);
                        }
                    }
                }


                //【12_P5】取得待通知的 vCard 回覆
                //
                string selStr12 = "SELECT pid, Scanner, Replier FROM vcard_send"
                  + " WHERE Status='Y' AND Pushed='N'"
                  + " ORDER BY ReplyTime, pid";
                OracleCommand selCmd12 = new OracleCommand(selStr12, connOracle);
                OracleDataAdapter adapter12 = new OracleDataAdapter(selCmd12);
                DataSet dataSet12 = new DataSet();
                adapter12.Fill(dataSet12);

                foreach (DataRow row in dataSet12.Tables[0].Rows)
                {
                    UInt32 vCardExchID = Convert.ToUInt32(row["pid"]);
                    PIDList.Add(Convert.ToString(vCardExchID));

                    const string MsgType = "VCF";
                    string Scanner = Convert.ToString(row["Scanner"]);
                    string Replier = Convert.ToString(row["Replier"]);
                    string PushMsg = string.Format("{0} replied your vCard.", Kit.SipKit.getCustomName(Replier));

                    DevicePushToken pToken = getPushToken(Scanner, connOracle);

                    if (pToken != null)
                    {
                        if (pToken.PushType == "IP" || pToken.PushType == "IS")
                        {
                            //iOS APNs Push
                            bool isDev = (pToken.PushType == "IS");
                            Kit.ApnsKit.SendMsgPushInHouse(MsgType, PushMsg, null, Replier, pToken.PushToken, isDev, vCardExchID, expiryTime);
                            Kit.ApnsKit.SendMsgPushAppStore(MsgType, PushMsg, null, Replier, pToken.PushToken, isDev, vCardExchID, expiryTime);
                        }
                        else if (pToken.PushType == "A")
                        {
                            //Android GCM Push
                            Kit.GCMKit.SendMsgPush(MsgType, PushMsg, Replier, pToken.PushToken, expirySeconds);
                        }
                        else if (pToken.PushType == "B")
                        {
                            //百度雲推送
                            Baidu.BaiduKit.SendMsgPush(MsgType, PushMsg, Replier, pToken.PushToken, expirySeconds);
                        }
                    }
                }


                //【13_P5】更新 vCard 交換請求的通知狀態
                //
                if (PIDList.Count > 0)
                {
                    string updStr13 = "UPDATE vcard_send SET Pushed='Y'"
                      + " WHERE Status IN ('0','Y') AND Pushed='N' AND pid IN "
                      + string.Format("({0})", string.Join(",", PIDList.ToArray()));
                    using (OracleCommand updCmd13 = new OracleCommand(updStr13, connOracle))
                    {
                        updCmd13.ExecuteNonQuery();
                    }
                }


                //【14_P6】取得待通知的 vCard 群組名片交換
                //
                string selStr14 = "SELECT m2.VGEID, m2.MemberID, COUNT(*) AS CNT"
                  + " FROM vcard_ge_main m1, vcard_ge_member m2, vcard_ge_dl d"
                  + " WHERE m1.Status='F' AND m1.Pushed='N'"
                  + "   AND m1.pid=m2.VGEID"
                  + "   AND m2.VGEID=d.VGEID AND m2.MemberID=d.MemberID"
                  + " GROUP BY m2.VGEID, m2.MemberID"
                  + " ORDER BY m2.VGEID, m2.JoinTime";
                OracleCommand selCmd14 = new OracleCommand(selStr14, connOracle);
                OracleDataAdapter adapter14 = new OracleDataAdapter(selCmd14);
                DataSet dataSet14 = new DataSet();
                adapter14.Fill(dataSet14);

                PIDList.Clear();

                foreach (DataRow row in dataSet14.Tables[0].Rows)
                {
                    UInt32 VGEID = Convert.ToUInt32(row["VGEID"]);
                    string MemberID = Convert.ToString(row["MemberID"]);
                    int vCount = Convert.ToInt32(row["CNT"]);
                    string from = string.Format("VGE_{0}", VGEID);

                    string VGEIDStr = VGEID.ToString();
                    if (!PIDList.Contains(VGEIDStr))
                    {
                        PIDList.Add(VGEIDStr);
                    }

                    const string MsgType = "VCF";
                    string PushMsg = string.Format("You got {0} new vCards!", vCount);

                    DevicePushToken pToken = getPushToken(MemberID, connOracle);

                    if (pToken != null)
                    {
                        if (pToken.PushType == "IP" || pToken.PushType == "IS")
                        {
                            //iOS APNs Push
                            bool isDev = (pToken.PushType == "IS");
                            Kit.ApnsKit.SendMsgPushInHouse(MsgType, PushMsg, null, from, pToken.PushToken, isDev, VGEID, expiryTime);
                            Kit.ApnsKit.SendMsgPushAppStore(MsgType, PushMsg, null, from, pToken.PushToken, isDev, VGEID, expiryTime);
                        }
                        else if (pToken.PushType == "A")
                        {
                            //Android GCM Push
                            Kit.GCMKit.SendMsgPush(MsgType, PushMsg, from, pToken.PushToken, expirySeconds);
                        }
                        else if (pToken.PushType == "B")
                        {
                            //百度雲推送
                            Baidu.BaiduKit.SendMsgPush(MsgType, PushMsg, from, pToken.PushToken, expirySeconds);
                        }
                    }
                }


                //【15_P6】更新 vCard 群組名片交換的通知狀態
                //
                if (PIDList.Count > 0)
                {
                    string updStr15 = "UPDATE vcard_ge_main SET Pushed='Y'"
                      + " WHERE Status='F' AND Pushed='N' AND pid IN "
                      + string.Format("({0})", string.Join(",", PIDList.ToArray()));
                    using (OracleCommand updCmd15 = new OracleCommand(updStr15, connOracle))
                    {
                        updCmd15.ExecuteNonQuery();
                    }
                }


            }
            catch (Exception ex)
            {
                string errorMsg = Kit.ChrisKit.Ex2Xml(ex, true);
                Kit.ChrisKit.AppendToDayLog(errorMsg);
            }
            finally
            {
                connOracle.Close();
            }
        }



        /// <summary>
        /// 計算未讀訊息筆數
        /// </summary>
        /// <param name="SIP_Num">SIP 帳號</param>
        /// <param name="connMySql">已連線的MySqlConnection</param>
        /// <returns>SIP 帳號的未讀訊息筆數</returns>
        //private static UInt32 getUnreadMsgCount(string SIP_Num, MySqlConnection connMySql)
        private static UInt32 getUnreadMsgCount(string SIP_Num, OracleConnection connOracle)
        {
            UInt32 count = 0;

            try
            {
                //【01】單人聊天未讀
                /* MySQL寫法
                string selStr1 = "SELECT COUNT(*) FROM ccpsip.msg_one"
                  + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                  + "   AND Receiver=?Receiver";
                MySqlCommand selCmd1 = new MySqlCommand(selStr1, connMySql);
                selCmd1.Parameters.Add("?Receiver", MySqlDbType.VarChar).Value = SIP_Num;
                count += Convert.ToUInt32(selCmd1.ExecuteScalar());
                */

                string selStr1 = "SELECT COUNT(*) FROM msg_one"
                  + " WHERE Status IN ('0','2') AND ReadTime IS NULL"
                  + "   AND Receiver=:Receiver";
                OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
                selCmd1.Parameters.Add(":Receiver", OracleType.VarChar).Value = SIP_Num;
                count += Convert.ToUInt32(selCmd1.ExecuteScalar());

                //【02】群組聊天未讀
                /*MySQL寫法
                string selStr2 = "SELECT COUNT(*)"
                  + " FROM ccpsip.group_msg_read r, ccpsip.group_msg m"
                  + " WHERE r.GMsgID=m.pid AND m.Status<>'W'"
                  + "   AND r.ReadTime IS NULL AND r.Receiver=?Receiver";
                MySqlCommand selCmd2 = new MySqlCommand(selStr2, connMySql);
                selCmd2.Parameters.Add("?Receiver", MySqlDbType.VarChar).Value = SIP_Num;
                count += Convert.ToUInt32(selCmd2.ExecuteScalar());
                */

                string selStr2 = "SELECT COUNT(*)"
                  + " FROM group_msg_read r, group_msg m"
                  + " WHERE r.GMsgID=m.pid AND m.Status<>'W'"
                  + "   AND r.ReadTime IS NULL AND r.Receiver=:Receiver";
                OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle);
                selCmd2.Parameters.Add(":Receiver", OracleType.VarChar).Value = SIP_Num;
                count += Convert.ToUInt32(selCmd2.ExecuteScalar());
            }
            catch (Exception ex)
            {
                Kit.ChrisKit.SkipVar(ex);
            }

            return count;
        }


        /// <summary>
        /// 檢查訊息提醒是否開啟
        /// </summary>
        /// <param name="SIP_Num">SIP 帳號</param>
        /// <param name="ChatID">聊天ID(對方帳號或ClientGID)</param>
        /// <param name="connMySql">已連線的MySqlConnection</param>
        /// <returns>訊息提醒開啟為true，否則false</returns>
        //private static bool isNotifyOn(string SIP_Num, string ChatID, MySqlConnection connMySql)
        private static bool isNotifyOn(string SIP_Num, string ChatID, OracleConnection connOracle)
        {
            /* MySQL寫法
            string selStr1 = "SELECT COUNT(*) FROM ccpsip.msg_notify_off"
              + " WHERE DisableTime IS NULL AND SIP_Num=?SIP_Num AND ChatID=?ChatID";
            MySqlCommand selCmd1 = new MySqlCommand(selStr1, connMySql);
            selCmd1.Parameters.Add("?SIP_Num", MySqlDbType.VarChar).Value = SIP_Num;
            selCmd1.Parameters.Add("?ChatID", MySqlDbType.VarChar).Value = ChatID;
            int count = Convert.ToInt32(selCmd1.ExecuteScalar());
            return (count == 0);
            */

            string selStr1 = "SELECT COUNT(*) FROM msg_notify_off"
              + " WHERE DisableTime IS NULL AND SIP_Num=:SIP_Num AND ChatID=:ChatID";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add(":SIP_Num", OracleType.VarChar).Value = SIP_Num;
            selCmd1.Parameters.Add(":ChatID", OracleType.VarChar).Value = ChatID;
            int count = Convert.ToInt32(selCmd1.ExecuteScalar());
            return (count == 0);
        }


        #region 取得 Device Push Token


        /// <summary>
        /// 取得 SIP 帳號對應的 Device Push Token
        /// </summary>
        /// <param name="SIP_Num">SIP 帳號</param>
        /// <param name="connMySql">已連線的MySqlConnection</param>
        /// <returns>SIP 帳號對應的 Device Push Token</returns>
        //private static DevicePushToken getPushToken(string SIP_Num, MySqlConnection connMySql)
        private static DevicePushToken getPushToken(string SIP_Num, OracleConnection connOracle)
        {
            /* MySQL寫法
            string selStr1 = "SELECT u.SIP_Num, t.UUID, t.PushType, t.DeviceToken"
              + " FROM ccpsip.reg_user u, ccpsip.device_token t"
              + " WHERE u.SIP_Num=?SIP_Num AND u.Status='Y'"
              + " AND t.Status='Y' AND u.UUID=t.UUID";
            MySqlCommand selCmd1 = new MySqlCommand(selStr1, connMySql);
            selCmd1.Parameters.Add("?SIP_Num", MySqlDbType.VarChar).Value = SIP_Num;
            MySqlDataAdapter adapter1 = new MySqlDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);
            */

            string selStr1 = "SELECT u.SIP_Num, t.UUID, t.PushType, t.DeviceToken"
              + " FROM reg_user u, device_token t"
              + " WHERE u.SIP_Num=:SIP_Num AND u.Status='Y'"
              + " AND t.Status='Y' AND u.UUID=t.UUID";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add(":SIP_Num", OracleType.VarChar).Value = SIP_Num;
            OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);

            if (dataSet1.Tables[0].Rows.Count > 0)
            {
                DataRow row = dataSet1.Tables[0].Rows[0];
                DevicePushToken deviceToken = new DevicePushToken(row);
                if (!string.IsNullOrEmpty(deviceToken.PushToken))
                    return deviceToken;
            }
            return null;
        }


        /// <summary>
        /// 取得手機門號對應的 Device Push Tokens (Array，CCP權限優先)
        /// </summary>
        /// <param name="CountryCode">User的手機門號國碼</param>
        /// <param name="MobileNum">User的手機門號</param>
        /// <param name="connMySql">已連線的MySqlConnection</param>
        /// <returns>手機門號對應的 Device Push Token Array</returns>
        //private static DevicePushToken[] getPushTokens(string CountryCode, string MobileNum, MySqlConnection connMySql)
        private static DevicePushToken[] getPushTokens(string CountryCode, string MobileNum, OracleConnection connOracle)
        {
            /* MySQL寫法
            string selStr1 = "SELECT u.SIP_Num, t.UUID, t.PushType, t.DeviceToken"
              + " FROM ccpsip.reg_user u, ccpsip.device_token t"
              + " WHERE u.CountryCode=?CountryCode AND u.MobileNum=?MobileNum"
              + " AND u.UserType LIKE 'CCP%'"
              + " AND u.Status='Y' AND t.Status='Y' AND u.UUID=t.UUID"
              + " ORDER BY u.SIP_Num";
            MySqlCommand selCmd1 = new MySqlCommand(selStr1, connMySql);
            selCmd1.Parameters.Add("?CountryCode", MySqlDbType.VarChar).Value = CountryCode;
            selCmd1.Parameters.Add("?MobileNum", MySqlDbType.VarChar).Value = MobileNum;
            MySqlDataAdapter adapter1 = new MySqlDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);
            */

            string selStr1 = "SELECT u.SIP_Num, t.UUID, t.PushType, t.DeviceToken"
              + " FROM reg_user u, device_token t"
              + " WHERE u.CountryCode=:CountryCode AND u.MobileNum=:MobileNum"
              + " AND u.UserType LIKE 'CCP%'"
              + " AND u.Status='Y' AND t.Status='Y' AND u.UUID=t.UUID"
              + " ORDER BY u.SIP_Num";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add(":CountryCode", OracleType.VarChar).Value = CountryCode;
            selCmd1.Parameters.Add(":MobileNum", OracleType.VarChar).Value = MobileNum;
            OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);

            List<DevicePushToken> tokenList = new List<DevicePushToken>();
            if (dataSet1.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in dataSet1.Tables[0].Rows)
                {
                    DevicePushToken deviceToken = new DevicePushToken(row);
                    if (!string.IsNullOrEmpty(deviceToken.PushToken))
                        tokenList.Add(deviceToken);
                }
            }
            else
            {
                /* MySQL寫法
                string selStr2 = "SELECT u.SIP_Num, t.UUID, t.PushType, t.DeviceToken"
                  + " FROM ccpsip.reg_user u, ccpsip.device_token t"
                  + " WHERE u.CountryCode=?CountryCode AND u.MobileNum=?MobileNum"
                  + " AND u.Status='Y' AND t.Status='Y' AND u.UUID=t.UUID"
                  + " ORDER BY u.SIP_Num";
                MySqlCommand selCmd2 = new MySqlCommand(selStr2, connMySql);
                selCmd2.Parameters.Add("?CountryCode", MySqlDbType.VarChar).Value = CountryCode;
                selCmd2.Parameters.Add("?MobileNum", MySqlDbType.VarChar).Value = MobileNum;
                MySqlDataAdapter adapter2 = new MySqlDataAdapter(selCmd2);
                DataSet dataSet2 = new DataSet();
                adapter2.Fill(dataSet2);
                */

                string selStr2 = "SELECT u.SIP_Num, t.UUID, t.PushType, t.DeviceToken"
                  + " FROM reg_user u, device_token t"
                  + " WHERE u.CountryCode=:CountryCode AND u.MobileNum=:MobileNum"
                  + " AND u.Status='Y' AND t.Status='Y' AND u.UUID=t.UUID"
                  + " ORDER BY u.SIP_Num";
                OracleCommand selCmd2 = new OracleCommand(selStr2, connOracle);
                selCmd2.Parameters.Add(":CountryCode", OracleType.VarChar).Value = CountryCode;
                selCmd2.Parameters.Add(":MobileNum", OracleType.VarChar).Value = MobileNum;
                OracleDataAdapter adapter2 = new OracleDataAdapter(selCmd2);
                DataSet dataSet2 = new DataSet();
                adapter2.Fill(dataSet2);

                foreach (DataRow row in dataSet2.Tables[0].Rows)
                {
                    DevicePushToken deviceToken = new DevicePushToken(row);
                    if (!string.IsNullOrEmpty(deviceToken.PushToken))
                        tokenList.Add(deviceToken);
                }
            }
            return tokenList.ToArray();
        }


        /// <summary>
        /// 取得註冊資料 ID 對應的 Auth Device Push Token
        /// </summary>
        /// <param name="ApplyID">註冊資料ID</param>
        /// <param name="connMySql">已連線的MySqlConnection</param>
        /// <returns>註冊資料 ID 對應的 Auth Device Push Token</returns>
        //private static AuthPushToken getAuthPushToken(UInt32 ApplyID, MySqlConnection connMySql)
        private static AuthPushToken getAuthPushToken(UInt32 ApplyID, OracleConnection connOracle)
        {
            /* MySQL寫法
            string selStr1 = "SELECT DeviceUUID, PushType, DeviceToken"
              + " FROM ccpsip.reg_auth_device WHERE ApplyID=?ApplyID";
            MySqlCommand selCmd1 = new MySqlCommand(selStr1, connMySql);
            selCmd1.Parameters.Add("?ApplyID", MySqlDbType.Int32).Value = ApplyID;
            MySqlDataAdapter adapter1 = new MySqlDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);
            */

            string selStr1 = "SELECT DeviceUUID, PushType, DeviceToken"
              + " FROM reg_auth_device WHERE ApplyID=:ApplyID";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add(":ApplyID", OracleType.Number).Value = ApplyID;
            OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);

            if (dataSet1.Tables[0].Rows.Count > 0)
            {
                DataRow row = dataSet1.Tables[0].Rows[0];
                AuthPushToken deviceToken = new AuthPushToken(row);
                if (!string.IsNullOrEmpty(deviceToken.PushToken))
                    return deviceToken;
            }
            return null;
        }


        #endregion


        /// <summary>
        /// Device Push Token
        /// </summary>
        private class DevicePushToken
        {
            private string _SIPNumber;
            private string _UUID;
            private string _PushType;
            private string _PushToken;

            public string SIPNumber { get { return this._SIPNumber; } }
            public string UUID { get { return this._UUID; } }
            public string PushType { get { return this._PushType; } }
            public string PushToken { get { return this._PushToken; } }

            public DevicePushToken(DataRow row)
            {
                _SIPNumber = Convert.ToString(row["SIP_Num"]);
                _UUID = Convert.ToString(row["UUID"]);
                _PushType = Convert.ToString(row["PushType"]);
                _PushToken = Convert.ToString(row["DeviceToken"]);
            }
        }


        /// <summary>
        /// Auth Device Push Token
        /// </summary>
        private class AuthPushToken
        {
            private string _UUID;
            private string _PushType;
            private string _PushToken;

            public string UUID { get { return this._UUID; } }
            public string PushType { get { return this._PushType; } }
            public string PushToken { get { return this._PushToken; } }

            public AuthPushToken(DataRow row)
            {
                _UUID = Convert.ToString(row["DeviceUUID"]);
                _PushType = Convert.ToString(row["PushType"]);
                _PushToken = Convert.ToString(row["DeviceToken"]);
            }
        }


    }
}